CREATE TABLE [dbo].[CampaignParticipation]
(
[CampaignParticipationKey] [uniqueidentifier] NOT NULL,
[CampaignKey] [uniqueidentifier] NOT NULL,
[RespondentUserKey] [uniqueidentifier] NOT NULL,
[ResponseTypeCode] [int] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL,
[OptOutSourceCodeKey] [uniqueidentifier] NULL,
[OptedOutOn] [datetime] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_CampaignParticipation_Insert]
    ON  [dbo].[CampaignParticipation]
    FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Fail the transaction if any inserted key is not in
    -- either the ContactMain or Prospect tables
    IF EXISTS(SELECT CampaignParticipationKey from inserted)
       AND NOT EXISTS(SELECT cm.ContactKey from ContactMain cm INNER JOIN inserted i ON cm.ContactKey=i.RespondentUserKey)
       AND NOT EXISTS(SELECT p.ProspectKey from Prospect p INNER JOIN inserted i ON p.ProspectKey=i.RespondentUserKey)
    BEGIN
        RAISERROR(N'One or more CampaignParticipation.RespondentUserKeys in being inserted reference neither a ContactKey nor a ProspectKey', 16, 1)
        ROLLBACK TRAN
    END
END

GO
CREATE TRIGGER [dbo].[asi_CampaignParticipation_Update]
    ON  [dbo].[CampaignParticipation]
    FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(RespondentUserKey)
    RETURN
    -- Fail the transaction if any inserted key is not in
    -- either the ContactMain or Prospect tables
    IF NOT EXISTS(SELECT cm.ContactKey from ContactMain cm INNER JOIN inserted i ON cm.ContactKey=i.RespondentUserKey)
       AND NOT EXISTS(SELECT p.ProspectKey from Prospect p INNER JOIN inserted i ON p.ProspectKey=i.RespondentUserKey)
    BEGIN
        RAISERROR(N'One or more CampaignParticipation.RespondentUserKeys in being inserted reference neither a ContactKey nor a ProspectKey', 16, 1)
        ROLLBACK TRAN
    END
END

GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [PK_CampaignParticipation] PRIMARY KEY CLUSTERED ([CampaignParticipationKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_CampaignKey] ON [dbo].[CampaignParticipation] ([CampaignKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_CreatedByUserKey] ON [dbo].[CampaignParticipation] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_OptOutSourceCodeKey] ON [dbo].[CampaignParticipation] ([OptOutSourceCodeKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_RespondentUserKey] ON [dbo].[CampaignParticipation] ([RespondentUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_ResponseTypeCode] ON [dbo].[CampaignParticipation] ([ResponseTypeCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignParticipation_UpdatedByUserKey] ON [dbo].[CampaignParticipation] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [FK_CampaignParticipation_CampaignMain] FOREIGN KEY ([CampaignKey]) REFERENCES [dbo].[CampaignMain] ([CampaignKey])
GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [FK_CampaignParticipation_ResponseTypeRef] FOREIGN KEY ([ResponseTypeCode]) REFERENCES [dbo].[ResponseTypeRef] ([ResponseTypeCode])
GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [FK_CampaignParticipation_SourceCode] FOREIGN KEY ([OptOutSourceCodeKey]) REFERENCES [dbo].[SourceCode] ([SourceCodeKey])
GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [FK_CampaignParticipation_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[CampaignParticipation] ADD CONSTRAINT [FK_CampaignParticipation_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO